package com.hongruan.dao.impl;

import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;

import com.hongruan.bean.DataIntegra;
import com.hongruan.dao.ReportDao;

@Repository
public class ReportDaoImpl implements ReportDao {

	@Resource
    private SessionFactory sessionFactory;
    protected Session getCurrentSession() {
        return this.sessionFactory.getCurrentSession();
    }
	public SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

	@SuppressWarnings("unchecked")
	public List getListBySwjg(String menuUuid,String swjgUuid,String startdate, String stopdate) {
		String hql = "select a.menuId,a.menuName,b.dealcount " +
				"from (select m.id as menuId,m.name as menuName " +
				"		from t_menu_t_menu mm " +
				"		left join t_menu m on  mm.childMenu_id = m.id " +
				"		where mm.t_menu_id = :menuUuid order by m.id) a " +
				"left join (select menuUuid,count(*) as dealcount " +
				"			from t_data_integra d where to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') >= :startdate " +
				"			and to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') <= :stopdate " +
				"			and swjgUuid = :swjgUuid group by menuUuid order by menuUuid) b " +
				"on a.menuId = b.menuUuid";
		Query query = this.getCurrentSession().createSQLQuery(hql);
		query.setParameter("swjgUuid", swjgUuid);
		query.setParameter("menuUuid", menuUuid);
		query.setParameter("startdate", startdate);
		query.setParameter("stopdate", stopdate);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List getChildListBySwjg(String menuUuid,String swjgUuid,String startdate, String stopdate) {
		String hql = "select a.menuId,a.menuName,b.dealcount " +
				"from (select m.formid as menuId,m.name as menuName " +
				"		from t_menu_t_menu mm " +
				"		left join t_menu m on  mm.childMenu_id = m.id " +
				"		where mm.t_menu_id = :menuUuid order by m.id) a " +
				"   left join (select menuUuid,count(*) as dealcount " +
				"			from t_data_integra d where to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') >= :startdate " +
				"			and to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') <= :stopdate " +
				"			and swjgUuid = :swjgUuid group by menuUuid order by menuUuid) b " +
				"on a.menuId = b.menuUuid";
		Query query = this.getCurrentSession().createSQLQuery(hql);
		query.setParameter("swjgUuid", swjgUuid);
		query.setParameter("menuUuid", menuUuid);
		query.setParameter("startdate", startdate);
		query.setParameter("stopdate", stopdate);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List getListBySjd(String swjgUuid, String startdate, String stopdate) {
		String hql = "select d.dealtime,d.nsrsbh,d.nsrmc,d.menuName,d.childMenuName from t_data_integra d "+
				"where d.swjgUuid = :swjgUuid  and to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss')  >= :startdate and to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss')  <= :stopdate ";
		Query query = this.getCurrentSession().createSQLQuery(hql);
		query.setParameter("swjgUuid", swjgUuid);
		query.setParameter("startdate", startdate);
		query.setParameter("stopdate", stopdate);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List getListByCzry(String swjgUuid, String startdate, String stopdate) {
		String hql = "select czryName,menuName,count(*) " +
				"from t_data_integra d  " +
				"where to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') >= :startdate " +
				"and to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') <= :stopdate " +
				"and swjgUuid = :swjgUuid group by menuName,czryname order by menuName,czryname";
		Query query = this.getCurrentSession().createSQLQuery(hql);
		query.setParameter("swjgUuid", swjgUuid);
		query.setParameter("startdate", startdate);
		query.setParameter("stopdate", stopdate);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List listSwjgBySuperId(String superSwjgUuid) {
		String hql = "select id as swjgId,mc as swjgName from t_swjg where superswjgid = :superSwjgUuid and isdel = 0";
		Query query = this.getCurrentSession().createSQLQuery(hql);
		query.setParameter("superSwjgUuid", superSwjgUuid);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List getListMenuById(String menuUuid) {
		String hql = "select m.id as menuId,m.name as menuName from t_menu_t_menu mm left join t_menu m on  mm.childMenu_id = m.id where mm.t_menu_id = :menuUuid order by m.id ";
		Query query = this.getCurrentSession().createSQLQuery(hql);
		query.setParameter("menuUuid", menuUuid);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List getTotal(String menuUuid,String startdate, String stopdate) {
		String hql = "select a.menuId,a.menuName,b.dealcount " +
		"from (select m.id as menuId,m.name as menuName " +
		"		from t_menu_t_menu mm " +
		"		left join t_menu m on  mm.childMenu_id = m.id " +
		"		where mm.t_menu_id = :menuUuid order by m.id) a " +
		"left join (select menuUuid,count(*) as dealcount " +
		"			from t_data_integra d where to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') >= :startdate " +
		"			and to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') <= :stopdate  and d.swjguuid != '-'" +
		"			group by menuUuid order by menuUuid) b " +
		"on a.menuId = b.menuUuid";
		Query query = this.getCurrentSession().createSQLQuery(hql);
		query.setParameter("menuUuid", menuUuid);
		query.setParameter("startdate", startdate);
		query.setParameter("stopdate", stopdate);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<DataIntegra> getDetail(String menuUuid, String swjgUuid,
			String startdate, String stopdate) {
		String hql = "from DataIntegra d where d.swjgUuid = :swjgUuid and d.menuUuid = :menuUuid and to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') >= :startdate and to_char(d.dealtime,'yyyy-MM-dd hh:mm:ss') <= :stopdate order by d.dealtime";
		Query query = this.getCurrentSession().createQuery(hql);
		query.setParameter("swjgUuid", swjgUuid);
		query.setParameter("menuUuid", menuUuid);
		query.setParameter("startdate", startdate);
		query.setParameter("stopdate", stopdate);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List getFormById(String formId) {
		String sql = "select f.pagename from t_form f where id = :formId";
		Query query = this.getCurrentSession().createSQLQuery(sql);
		query.setParameter("formId", formId);
		return query.list();
	}
}
